# os模块
import os, openpyxl, shutil
from openpyxl.styles import Alignment, Border, Side, Font, PatternFill
base_path = "C:/Users/Administrator/Desktop/python测试创建文件夹"
file = "python测试.xlsx"
# excel的路径
# 拼接路径
# 会根据操作系统的不同拼接出斜杠\或者反斜杠/,所以以后路径尽量使用拼接的
file_excel_path = os.path.join(base_path, file)

# 对齐方式
def show_alignment(cell):
    # horizontal 水平方向  left  right center
    # vertical:垂直方向 top center bottom
    cell.alignment = Alignment(horizontal="center", vertical="center")
# 边框
def show_border(cell):
    cell.border = Border(
        top=Side(style="thin", color="0000CD"),
        bottom=Side(style="thin", color="0000CD"),
        left=Side(style="thin", color="0000CD"),
        right=Side(style="thin", color="0000CD")
    )
# 字体
def show_font(cell):
    cell.font = Font(name="微软雅黑", size=40, color="FF4500")
# 背景色
def show_fill(cell):
    cell.fill = PatternFill("solid", fgColor="FFEC88")
# 写到excel（新的）
def write_excel():
    # 创建文件夹
    if not os.path.exists(base_path):
        os.makedirs(base_path)
    # 创建新的工作簿
    workbook = openpyxl.Workbook()
    # 创建工作表
    sheet = workbook.create_sheet("景区天气")
    # 这里append没有提示，如果append 里面是字符串会报错
    lst = [
            ['景区', '天气', '气温', '旅游指数'],
            ['三亚', '多云', '26/34℃', '适宜'],
            ['九寨沟', '阵雨', '20/29℃', '适宜'],
            ['大理', '小雨', '16/26℃', '适宜'],
            ['张家界', '多云转小雨', '24/37℃', '适宜'],
            ['桂林', '晴', '27/37℃', '较适宜'],
            ['青岛', '小雨', '26/30℃', '适宜']
           ]
    for item in lst:
        sheet.append(item)
    workbook.save(file_excel_path)
    workbook.close()
    print("写出完成")
# 追加写excel
def write_excel_1():
    workbook = openpyxl.load_workbook(file_excel_path)
    sheet = workbook.worksheets[0]
    cell = sheet.cell(1, 4)
    cell.value = "结果"
    show_alignment(cell)
    show_border(cell)
    show_font(cell)
    show_fill(cell)
    workbook.save(file_excel_path)
    print("写出完成")
# 读取excel
def read_excel():
    # 读取excel数据
    workbook = openpyxl.load_workbook(file_excel_path)
    sheet = workbook['景区天气']
    lst = []
    for row in sheet.rows:
        slts = []
        for cell in row:
            slts.append(cell.value)
        lst.append(slts)
    for item in lst:
        print(item)
    print("读取完成")
# 读取excel
def read_excel_1():
    # 读取excel数据
    workbook = openpyxl.load_workbook(file_excel_path)
    sheet = workbook.worksheets[1]
    cell = sheet.cell(1, 1)
    print(cell.value)
# 从指定行开始读取excel
def read_excel_2():
    # 读取excel数据
    workbook = openpyxl.load_workbook(file_excel_path)
    sheet = workbook['景区天气']
    lst = []
    # 这个表示从第二行开始读取,到第四行结束
    for row in sheet.iter_rows(min_row=2, max_row=4):
        slts = []
        for cell in row:
            slts.append(cell.value)
        lst.append(slts)
    for item in lst:
        print(item)
    print("读取完成")
# 显示sheet名称
def show_sheet():
    workbook = openpyxl.load_workbook(file_excel_path)
    print("显示所有sheet工作簿的名称：", workbook.sheetnames)
    print("显示所有sheet工作簿的对象：", workbook.worksheets)
# 单元格操作
def show_row():
    workbook = openpyxl.load_workbook(file_excel_path)
    sheet = workbook['景区天气']
    # 获取所有行
    all_row = sheet.rows
    # 获取指定行
    one_row = sheet[2]
    for cell in one_row:
        print(cell.value)

if __name__ == '__main__':
    xx()
